DatabaseAdmin
The following is a brief overview of the RT (3.0.x) schema and some tips on examining the database. Also, very occasionally you may wish to modify the database directly (e.g. delete bogus information). How to do these things is roughly described here. NOTE: Nobody guarantee that this document describes all relations in the database. NOTE: Making direct modifications to the database is not recommended, but is sometimes required. Should you do so, ensure that you backup your database first, and ensure its integrity afterwards. Tables The RT DBSchema can be thought of in 5 broad groupings: * Groups and Users (groups, users, groupmembers, cachedgroupmembers, principals, acl) * Templates and Scrips (scripconditions, scripactions, scrips, templates) * Infrastructure (queues, customfields, customfieldvalues) * Tickets and related information (tickets, transactions, ticketcustomfieldvalues, links, attachments) * Sessions In mysql or postgresql you can use the following commands to examine the schema: * help * help command * \dt [ table ] * \ds [ sequence ] Relations Each object is identified uniquely by an integer id (sequence) which is incremented for each new object. You can see the relationships among objects by following id linkages. e.g. * ticket -> queue * ticketcustomfieldvalue -> ticket * transaction -> ticket * link -> target * attachment -> transactionid Danger way to delete tickets NOTE: Original author forget a lot of details. 1) RT creates role groups for each ticket. 2) Attachments have parent/child relationship. 3) Each role group has principal record. 4) each role group can have members 5) each reacord in group members has one or more related records in cached group members. IMHO you shouldn't use this process to delete any records in RT DB --RuslanZakirov NOTE: Also original author suggest to use copy&paste method to delete related records that is bad dy design. --RuslanZakirov If you really must delete or modify an object, first determine all linkages to that object. e.g. * select * from tickets where id = ; * select id from transactions where ticket = ; * select id from ticketcustomfieldvalues where ticket = ; * select id from links where target = ; * select id from attachments where transactionid = ; Then to delete a given ticket, use the following: * delete from attachments where id in ( ); * delete from links where id in ( ); * delete from ticketcustomfieldvalues where id in ( ); * delete from transactions where id in ( ); * delete from tickets where id = ; If you have a large number of bogus tickets to delete you could use a script (perl, shell, sql) to determine which tickets need to be deleted and to do the deletion. ANOTHER NOTE: As a new ticket is being inserted, records are also generated in the following tables: principals, cachedgroupmembers, groupmembers, groups. So, as you delete a ticket, you also want to purge records that directly or indirectly reference a range of tickets to be deleted from these tables. DO NOT USE THIS RECIPE. PLEASE USE RT SHREDDER, BUILT IN TO RT 3.8 AND NEWER You may want to do something like this (in addition to the steps above): 1) define tickets to delete: $tickets_to_delete = "age(tickets.resolved) > '6 months'"; 2) delete tickets and all related records: - delete from principals where id=groups.id and groups.instance=tickets.id as int) and $tickets_to_delete; - delete from cachedgroupmembers where groupid=groups.id and groups.instance=tickets.id and $tickets_to_delete; - delete from groupmembers where groupid=groups.id and groups.instance=tickets.id and $tickets_to_delete; - delete from groups where instance=tickets.id and $tickets_to_delete; -AnyaFiglin